﻿using SIG.DAL.Dapper.Model;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SIG.Infrastructure.Configs;

namespace SIG.DAL.Dapper.Repositories
{
    public class EmailTemplateRepository
    {
        private DbConnection _connection;

        public EmailTemplate GetById(int id)
        {
            EmailTemplate result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Get<EmailTemplate>(id);
            }
            return result;
        }
        public int? Create(EmailTemplate model)
        {
            int? result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Insert(model);
            }
            return result;
        }

        public int Update(EmailTemplate model)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Update(model);
            }
            return result;
        }
        public int Delete(EmailTemplate model)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Delete(model);
            }
            return result;
        }

        public int Delete(int id)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Delete<EmailTemplate>(id);
            }
            return result;
        }

     

        public IEnumerable<EmailTemplate> GetItems()
        {
            IEnumerable<EmailTemplate> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.GetList<EmailTemplate>();
            }
            return result;
        }

        public EmailTemplate GetEmailTemplateByTemplateNo(string templateNo)
        {
            EmailTemplate result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.GetList<EmailTemplate>(new { TemplateNo = templateNo }).FirstOrDefault();
            }
            return result;
        }

        public bool IsExistTemplate(string templateNo)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
               
                result = _connection.RecordCount<EmailTemplate>("Where TemplateNo = @TemplateNo", new { TemplateNo = templateNo });
            }
            return result > 0;

        }
        public bool IsExistTemplate(string templateNo, int id)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {               
                result = _connection.RecordCount<EmailTemplate>("Where TemplateNo = @TemplateNo and Id <> @Id", new { TemplateNo = templateNo, Id = id });
            }
            return result > 0;
        }


        public IEnumerable<EmailTemplate> GetListPagedAcitveItems(int pageIndex, int pageSize)
        {
            IEnumerable<EmailTemplate> result;
            using (_connection = Utilities.GetOpenConnection())
            {               
                result = _connection.GetListPaged<EmailTemplate>(pageIndex, pageSize, "where Active=1", "CreatedDate desc");
            }
            return result;
        }

        public IEnumerable<EmailTemplate> GetListPagedAcitveItems(int pageIndex, int pageSize, out int totalCount)
        {
            IEnumerable<EmailTemplate> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                totalCount = _connection.RecordCount<EmailTemplate>("where Active=1");
                result = _connection.GetListPaged<EmailTemplate>(pageIndex, pageSize, "where Active=1", "CreatedDate desc");
            }
            return result;
        }
       
      
        public IEnumerable<EmailTemplate> GetListPagedItems(int pageIndex, int pageSize, out int totalCount)
        {
            IEnumerable<EmailTemplate> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                totalCount = _connection.RecordCount<EmailTemplate>();
                result = _connection.GetListPaged<EmailTemplate>(pageIndex, pageSize, null, "CreatedDate desc");
            }
            return result;
        }

        public IEnumerable<EmailTemplate> GetListPagedItems(int pageIndex, int pageSize, string keyword, int accountId, out int totalCount)
        {
            IEnumerable<EmailTemplate> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                keyword = !string.IsNullOrEmpty(keyword) ? keyword.Replace("[", "[[]").Replace("%", "[%]") : keyword;
                string likeKeyword = "%" + keyword + "%";

                var sql = "where 1=1 ";
                
                if (!string.IsNullOrEmpty(keyword))
                {                   
                    sql = sql + " and (Subject like @Keyword or TemplateNo like @Keyword)";
                }
                if (accountId > 0)
                {
                    sql = sql + " and EmailAccountId = @AccountId ";
                }

                totalCount = _connection.RecordCount<EmailTemplate>(sql,
                    new { Keyword = likeKeyword, AccountId = accountId });
                result = _connection.GetListPaged<EmailTemplate>(pageIndex, pageSize, sql, "CreatedDate desc",
                    new { Keyword = likeKeyword, AccountId = accountId });

            }
            return result;
        }

        public string ReplaceTemplate(string body)
        {
            body = body.Replace("{SiteName}", SettingsManager.Site.SiteName);
            body = body.Replace("{SiteURL}", SettingsManager.Site.SiteDomainName);

            return body;
        }
    }

}
